/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ipcc.manager.entity;

import com.telsoft.applet.util.StringUtil;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import telsoft.admin.util.AppServer;
import telsoft.sql.Database;

/**
 *
 * @author Administrator
 */

public class IPCC_EXTENSIONS implements Serializable,Cloneable {

    private String id;
    private String extension;
    private String created_date;
    private IPCC_EXTENSION_GROUPS ipcc_extension_groups = new IPCC_EXTENSION_GROUPS();
    private int status;
    private String password;    

    public IPCC_EXTENSIONS() {
    }

    public IPCC_EXTENSION_GROUPS getIpcc_extension_groups() {
        return ipcc_extension_groups;
    }

    public void setIpcc_extension_groups(IPCC_EXTENSION_GROUPS ipcc_extension_groups) {
        this.ipcc_extension_groups = ipcc_extension_groups;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id;
    }

    public String getExtension() {
        return extension;
    }

    public void setExtension(String extension) {
        this.extension = extension;
    }

    public String getCreated_date() {
        return created_date;
    }

    public void setCreated_date(String created_date) {
        this.created_date = created_date;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public static List<IPCC_EXTENSIONS> getAll(String condition,String userId) throws Exception {        
        List<IPCC_EXTENSIONS> list = new ArrayList<IPCC_EXTENSIONS>();
        Connection cn = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        String sql = "SELECT F.ID,F.EXTENSION,TO_CHAR(F.CREATED_DATE,'dd-mm-yyyy'),F.EXTENSION_GROUP,F.STATUS,F.PASSWORD"
                + " FROM SEC_USER A, SEC_GROUP B, SEC_GROUP_USER C, PARTNERS D, IPCC_EXTENSION_GROUPS E, IPCC_EXTENSIONS F"
                + " WHERE A.USER_ID = C.USER_ID AND B.GROUP_ID = C.GROUP_ID AND UPPER(B.GROUP_NAME) = UPPER(D.PARTNER_CODE)"
                + " AND E.PARTNER_ID = D.PARTNER_ID AND F.EXTENSION_GROUP = E.GROUP_ AND (A.USER_ID = ? OR ? = -1)"
                + " "+condition
                + " ORDER BY F.EXTENSION,F.CREATED_DATE";
        try {
            cn = AppServer.getConnection();
            pst = cn.prepareStatement(sql);
            
            if(userId == null){
                userId = "-1";
            }
            
            pst.setInt(1, Integer.parseInt(userId));
            pst.setInt(2, Integer.parseInt(userId));            
            rs = pst.executeQuery();
            while (rs.next()) {
                IPCC_EXTENSIONS i = new IPCC_EXTENSIONS();
                IPCC_EXTENSION_GROUPS eg = new IPCC_EXTENSION_GROUPS();
                
                eg.setGroup(rs.getString(4));
                i.setId(rs.getString(1));
                i.setExtension(rs.getString(2));
                i.setCreated_date(rs.getString(3));
                i.setIpcc_extension_groups(eg);
                i.setStatus(Integer.parseInt(rs.getString(5)));
                i.setPassword(rs.getString(6));
                list.add(i);
            }
            return list;
        } finally {
            Database.closeObject(rs);
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }

    public boolean insert() throws Exception {
        Connection cn = null;
        PreparedStatement pst = null;
        String strSQL = "";
        try {
            cn = AppServer.getConnection();
            setId(Database.getSequenceValue(cn, "seq_extension"));
            setStatus(1);
            Date d = new Date();
            setCreated_date(StringUtil.format(d, "dd-MM-yyyy"));
            strSQL = "INSERT INTO IPCC_EXTENSIONS(ID,EXTENSION,CREATED_DATE,EXTENSION_GROUP,STATUS,PASSWORD)"
                    + " VALUES(?,?,to_date(?,'dd-mm-yyyy'),?,1,?)";
            pst = cn.prepareStatement(strSQL);
            pst.setInt(1, Integer.parseInt(getId()));
            pst.setString(2, getExtension());
            pst.setString(3, getCreated_date());
            pst.setString(4, getIpcc_extension_groups().getGroup());
            pst.setString(5, getPassword());
            return pst.executeUpdate() > 0;
        } finally {
            Database.closeObject(pst);
            Database.closeObject(cn);
        }
    }

    public boolean remove() throws Exception {
        Connection cn = null;
        Statement st = null;
        String strSQL = "DELETE FROM IPCC_EXTENSIONS where ID = " + getId() + "";
        try {
            cn = AppServer.getConnection();
            st = cn.createStatement();
            return st.executeUpdate(strSQL) > 0;
        } finally {
            Database.closeObject(st);
            Database.closeObject(cn);
        }
    }

    public boolean update(Connection cn) throws Exception {        
        PreparedStatement pst = null;
        String strSQL = "UPDATE IPCC_EXTENSIONS"
                + " SET EXTENSION = ?,"
                + " EXTENSION_GROUP = ?,"
                + " STATUS = ?,"
                + " PASSWORD = ?"
                + " WHERE ID = ?";
        try {
            pst = cn.prepareStatement(strSQL);
            pst.setString(1, getExtension());
            pst.setString(2, getIpcc_extension_groups().getGroup());
            pst.setInt(3, getStatus());
            pst.setString(4, getPassword());
            pst.setInt(5, Integer.parseInt(getId()));
            
            return pst.executeUpdate() > 0;
            
        } finally {
            Database.closeObject(pst);
        }
    }
    
    @Override
    public Object clone() throws CloneNotSupportedException{
        return super.clone();
    }
}
